<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0,viewport-fit=cover"><title>sql学习 | 云起</title><meta name="author" content="zjaaa"><meta name="copyright" content="zjaaa"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="Sql语句学习一.Sql数据分类1.DQL(数据查询语言) DATA QUERY LANGUAGE 查询语句 凡是select语句都为DQL 2.DML(数据操作语言) DATA MANIPULATION LANGUAGE insert delete update 对表当中数据进行增删改 3.DDL(数据定义语言)DATA DEFINITION LANGUAGEcreate drop alter">
<meta property="og:type" content="article">
<meta property="og:title" content="sql学习">
<meta property="og:url" content="https://52zja.top/2024/02/06/sql%E5%AD%A6%E4%B9%A0/index.html">
<meta property="og:site_name" content="云起">
<meta property="og:description" content="Sql语句学习一.Sql数据分类1.DQL(数据查询语言) DATA QUERY LANGUAGE 查询语句 凡是select语句都为DQL 2.DML(数据操作语言) DATA MANIPULATION LANGUAGE insert delete update 对表当中数据进行增删改 3.DDL(数据定义语言)DATA DEFINITION LANGUAGEcreate drop alter">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://52zja.top/img/icon.jpg">
<meta property="article:published_time" content="2024-02-06T11:43:21.000Z">
<meta property="article:modified_time" content="2024-02-29T11:21:25.138Z">
<meta property="article:author" content="zjaaa">
<meta property="article:tag" content="数据库">
<meta property="article:tag" content="sql">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://52zja.top/img/icon.jpg"><link rel="shortcut icon" href="/img/icon2.png"><link rel="canonical" href="https://52zja.top/2024/02/06/sql%E5%AD%A6%E4%B9%A0/index.html"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css?v=4.13.0"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@6.5.1/css/all.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui@5.0.33/dist/fancybox/fancybox.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = {
  root: '/',
  algolia: undefined,
  localSearch: undefined,
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlight.js","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  dateSuffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: {"limitCount":50,"languages":{"author":"作者: zjaaa","link":"链接: ","source":"来源: 云起","info":"著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。"}},
  lightbox: 'fancybox',
  Snackbar: undefined,
  infinitegrid: {
    js: 'https://cdn.jsdelivr.net/npm/@egjs/infinitegrid@4.11.1/dist/infinitegrid.min.js',
    buttonText: '加载更多'
  },
  isPhotoFigcaption: false,
  islazyload: true,
  isAnchor: false,
  percent: {
    toc: true,
    rightside: false,
  },
  autoDarkmode: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'sql学习',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2024-02-29 19:21:25'
}</script><script>(win=>{
      win.saveToLocal = {
        set: (key, value, ttl) => {
          if (ttl === 0) return
          const now = Date.now()
          const expiry = now + ttl * 86400000
          const item = {
            value,
            expiry
          }
          localStorage.setItem(key, JSON.stringify(item))
        },
      
        get: key => {
          const itemStr = localStorage.getItem(key)
      
          if (!itemStr) {
            return undefined
          }
          const item = JSON.parse(itemStr)
          const now = Date.now()
      
          if (now > item.expiry) {
            localStorage.removeItem(key)
            return undefined
          }
          return item.value
        }
      }
    
      win.getScript = (url, attr = {}) => new Promise((resolve, reject) => {
        const script = document.createElement('script')
        script.src = url
        script.async = true
        script.onerror = reject
        script.onload = script.onreadystatechange = function() {
          const loadState = this.readyState
          if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
          script.onload = script.onreadystatechange = null
          resolve()
        }

        Object.keys(attr).forEach(key => {
          script.setAttribute(key, attr[key])
        })

        document.head.appendChild(script)
      })
    
      win.getCSS = (url, id = false) => new Promise((resolve, reject) => {
        const link = document.createElement('link')
        link.rel = 'stylesheet'
        link.href = url
        if (id) link.id = id
        link.onerror = reject
        link.onload = link.onreadystatechange = function() {
          const loadState = this.readyState
          if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
          link.onload = link.onreadystatechange = null
          resolve()
        }
        document.head.appendChild(link)
      })
    
      win.activateDarkMode = () => {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = () => {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
        if (t === 'dark') activateDarkMode()
        else if (t === 'light') activateLightMode()
      
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
      const detectApple = () => {
        if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
          document.documentElement.classList.add('apple')
        }
      }
      detectApple()
    })(window)</script><meta name="generator" content="Hexo 7.1.1"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src= "" data-lazy-src="/img/icon.jpg" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">10</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">19</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">3</div></a></div><hr class="custom-hr"/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page group hide" href="javascript:void(0);"><i class="fa-fw fa fa-sitemap"></i><span> 站点</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" target="_blank" rel="noopener" href="https://aqcoder.cn"><i class="fa-fw fa fa-paper-plane"></i><span> 我的导航</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://bimg.cc"><i class="fa-fw iconfont icon-Bing"></i><span> 必应壁纸</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://status.aqcoder.cn"><i class="fa-fw fa fa-heartbeat"></i><span> 站点状态</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://next.aqcoder.cn"><i class="fa-fw fa fa-folder"></i><span> 旧博客</span></a></li></ul></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fa fa-bookmark"></i><span> 博文</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-archive"></i><span> 分类</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fa fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/archives/"><i class="fa-fw fa fa-folder-open"></i><span> 归档</span></a></li></ul></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 生活</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/share/"><i class="fa-fw fa fa-share-alt-square"></i><span> 分享</span></a></li><li><a class="site-page child" href="/music/"><i class="fa-fw fa fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/gallery/"><i class="fa-fw fa fa-images"></i><span> 图库</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/links/"><i class="fa-fw fa fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" target="_blank" rel="noopener" href="https://travellings.cn"><i class="fa-fw fa fa-train"></i><span> 开往</span></a></div><div class="menus_item"><a class="site-page" target="_blank" rel="noopener" href="https://foreverblog.cn/go.html"><i class="fa-fw fa fa-circle"></i><span> 虫洞</span></a></div><div class="menus_item"><a class="site-page" href="/comment/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言板</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-user"></i><span> 关于我</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header"><nav id="nav"><span id="blog-info"><a href="/" title="云起"><span class="site-name">云起</span></a></span><div id="menus"><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page group hide" href="javascript:void(0);"><i class="fa-fw fa fa-sitemap"></i><span> 站点</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" target="_blank" rel="noopener" href="https://aqcoder.cn"><i class="fa-fw fa fa-paper-plane"></i><span> 我的导航</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://bimg.cc"><i class="fa-fw iconfont icon-Bing"></i><span> 必应壁纸</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://status.aqcoder.cn"><i class="fa-fw fa fa-heartbeat"></i><span> 站点状态</span></a></li><li><a class="site-page child" target="_blank" rel="noopener" href="https://next.aqcoder.cn"><i class="fa-fw fa fa-folder"></i><span> 旧博客</span></a></li></ul></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fa fa-bookmark"></i><span> 博文</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-archive"></i><span> 分类</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fa fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/archives/"><i class="fa-fw fa fa-folder-open"></i><span> 归档</span></a></li></ul></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 生活</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/share/"><i class="fa-fw fa fa-share-alt-square"></i><span> 分享</span></a></li><li><a class="site-page child" href="/music/"><i class="fa-fw fa fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/gallery/"><i class="fa-fw fa fa-images"></i><span> 图库</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/links/"><i class="fa-fw fa fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" target="_blank" rel="noopener" href="https://travellings.cn"><i class="fa-fw fa fa-train"></i><span> 开往</span></a></div><div class="menus_item"><a class="site-page" target="_blank" rel="noopener" href="https://foreverblog.cn/go.html"><i class="fa-fw fa fa-circle"></i><span> 虫洞</span></a></div><div class="menus_item"><a class="site-page" href="/comment/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言板</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-user"></i><span> 关于我</span></a></div></div><div id="toggle-menu"><a class="site-page" href="javascript:void(0);"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">sql学习</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2024-02-06T11:43:21.000Z" title="发表于 2024-02-06 19:43:21">2024-02-06</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2024-02-29T11:21:25.138Z" title="更新于 2024-02-29 19:21:25">2024-02-29</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0/">学习笔记</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="sql学习"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h1 id="Sql语句学习"><a href="#Sql语句学习" class="headerlink" title="Sql语句学习"></a>Sql语句学习</h1><h2 id="一-Sql数据分类"><a href="#一-Sql数据分类" class="headerlink" title="一.Sql数据分类"></a>一.Sql数据分类</h2><h3 id="1-DQL-数据查询语言"><a href="#1-DQL-数据查询语言" class="headerlink" title="1.DQL(数据查询语言)"></a>1.DQL(数据查询语言)</h3><p> DATA QUERY LANGUAGE<br> 查询语句 凡是select语句都为DQL</p>
<h3 id="2-DML-数据操作语言"><a href="#2-DML-数据操作语言" class="headerlink" title="2.DML(数据操作语言)"></a>2.DML(数据操作语言)</h3><p> DATA MANIPULATION LANGUAGE<br> insert delete update 对表当中数据进行增删改</p>
<h3 id="3-DDL-数据定义语言"><a href="#3-DDL-数据定义语言" class="headerlink" title="3.DDL(数据定义语言)"></a>3.DDL(数据定义语言)</h3><p>DATA DEFINITION LANGUAGE<br>create drop alter 对表结构的增删改</p>
<h3 id="4-TCL-事务控制语言"><a href="#4-TCL-事务控制语言" class="headerlink" title="4.TCL(事务控制语言)"></a>4.TCL(事务控制语言)</h3><p> commit提交事务，rollback回滚事务</p>
<h3 id="5-DCL（数据控制语言）"><a href="#5-DCL（数据控制语言）" class="headerlink" title="5.DCL（数据控制语言）"></a>5.DCL（数据控制语言）</h3><p> DATA CONTROL LANGUAGE<br> grant授权，revoke撤销权限</p>
<h2 id="二-mysql独特命令"><a href="#二-mysql独特命令" class="headerlink" title="二.mysql独特命令"></a>二.mysql独特命令</h2><h3 id="1-查看有哪些数据库"><a href="#1-查看有哪些数据库" class="headerlink" title="1.查看有哪些数据库"></a>1.查看有哪些数据库</h3><p><strong>show  databases；</strong></p>
<h3 id="2-创建属于自己的数据库"><a href="#2-创建属于自己的数据库" class="headerlink" title="2.创建属于自己的数据库"></a>2.创建属于自己的数据库</h3><p><strong>create database studybase</strong>;</p>
<h3 id="3-使用自己的数据库"><a href="#3-使用自己的数据库" class="headerlink" title="3.使用自己的数据库"></a>3.使用自己的数据库</h3><p><strong>use studybase;</strong></p>
<h3 id="4-查看当前数据库有哪些表"><a href="#4-查看当前数据库有哪些表" class="headerlink" title="4.查看当前数据库有哪些表"></a>4.查看当前数据库有哪些表</h3><p><strong>show tables</strong>;</p>
<h3 id="5-导入sql脚本"><a href="#5-导入sql脚本" class="headerlink" title="5.导入sql脚本"></a>5.导入sql脚本</h3><p><strong>source D:&#x2F;study&#x2F;study.sql</strong></p>
<h3 id="6-删除数据库"><a href="#6-删除数据库" class="headerlink" title="6.删除数据库"></a>6.删除数据库</h3><p><strong>drop database studybase</strong></p>
<h3 id="7-查询表数据结构"><a href="#7-查询表数据结构" class="headerlink" title="7.查询表数据结构"></a>7.查询表数据结构</h3><p><strong>desc studybase</strong></p>
<h3 id="8-查看当前使用的是哪个数据库"><a href="#8-查看当前使用的是哪个数据库" class="headerlink" title="8.查看当前使用的是哪个数据库"></a>8.查看当前使用的是哪个数据库</h3><p><strong>select database();</strong></p>
<h3 id="9-终止一条语句命令"><a href="#9-终止一条语句命令" class="headerlink" title="9.终止一条语句命令"></a>9.终止一条语句命令</h3><p><strong>\c</strong></p>
<h3 id="10-导出数据库"><a href="#10-导出数据库" class="headerlink" title="10.导出数据库"></a>10.导出数据库</h3><p><strong>mysqldump xxxdatabase;</strong></p>
<h2 id="三-查询语句详解"><a href="#三-查询语句详解" class="headerlink" title="三.查询语句详解"></a>三.查询语句详解</h2><h3 id="1-sql规则"><a href="#1-sql规则" class="headerlink" title="1.sql规则"></a>1.sql规则</h3><p><strong>任何一条sql语句以分号“;”结尾</strong></p>
<p><strong>sql语句不区分大小写</strong></p>
<p><strong>标准sql语句要求字符串使用单引号括起来</strong></p>
<p><strong>起别名的as可以用空格代替</strong></p>
<blockquote>
<p>举个例子</p>
<p>select t_no t_money as ‘工资‘ where t_name &#x3D; ‘zhangsan’</p>
<p>select t_no t_money  ‘工资‘ where t_name &#x3D; ‘zhangsan’</p>
<p>这两个语句查询效果一样</p>
</blockquote>
<h3 id="2-select关键字"><a href="#2-select关键字" class="headerlink" title="2.select关键字"></a>2.select关键字</h3><h4 id="1-null"><a href="#1-null" class="headerlink" title="1.null"></a>1.null</h4><blockquote>
<p>所有数据库只要运算有null参与结果均为null</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#ifnull关键字用法(可能为<span class="keyword">null</span>的数据，当做什么处理)</span><br><span class="line"><span class="keyword">select</span> ename,(sal<span class="operator">+</span>ifnull(comm,<span class="number">0</span>))<span class="operator">*</span><span class="number">12</span> <span class="keyword">from</span> emp</span><br></pre></td></tr></table></figure>



<p>null为空，查询u_name不为null值的所有用户</p>
<blockquote>
<p>select * from user where u_name is not null</p>
</blockquote>
<h4 id="2-between-…-and-…"><a href="#2-between-…-and-…" class="headerlink" title="2.between … and …"></a>2.between … and …</h4><p>两端都为闭区间 等价于&lt;&#x3D; … and &gt;&#x3D;…</p>
<blockquote>
<p>select * from user where money &gt;&#x3D;1000 and money&lt;&#x3D;3000</p>
<p>select * from user where money between 1000 and 3000</p>
</blockquote>
<h4 id="3-and-以及-or"><a href="#3-and-以及-or" class="headerlink" title="3.and 以及 or"></a>3.and 以及 or</h4><p>and表示并且</p>
<p>or表示或者</p>
<p>and优先级高</p>
<h4 id="4-in"><a href="#4-in" class="headerlink" title="4.in"></a>4.in</h4><p>in等同于or</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#查找sal工资等于<span class="number">1000</span>或者<span class="number">5000</span>的员工</span><br><span class="line"><span class="keyword">select</span> ename,job <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="keyword">in</span>(<span class="number">1000</span>,<span class="number">5000</span>)</span><br></pre></td></tr></table></figure>

<h4 id="5-like"><a href="#5-like" class="headerlink" title="5.like"></a>5.like</h4><blockquote>
<p>%表示任意多个字符</p>
<p>_表示一个字符</p>
</blockquote>
<h4 id="6-order-by"><a href="#6-order-by" class="headerlink" title="6.order by"></a>6.order by</h4><blockquote>
<p>排序 </p>
<p>asc为升序 desc为降序 默认为asc</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal</span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">asc</span></span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span></span><br><span class="line">#按照工资降序排列，工资相同时，按照名字升序排列</span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span> , ename <span class="keyword">asc</span> </span><br></pre></td></tr></table></figure>

<h4 id="7-分组函数"><a href="#7-分组函数" class="headerlink" title="7.分组函数"></a>7.分组函数</h4><blockquote>
<p>count 计数 sum求和 avg平均值 max最大值 min最小值</p>
<p>也叫多行处理函数 输入多行 最终输出结果为一行</p>
<p>分组函数自动忽略null !!!</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">#找出工资总和</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">sum</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line">#找出最高工资</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line">#找出最低工资</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">min</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line">#找出平均工资</span><br><span class="line"><span class="keyword">select</span> ave(sal) <span class="keyword">from</span> emp;</span><br><span class="line">#找出总人数</span><br><span class="line">#<span class="built_in">count</span>(<span class="operator">*</span>)统计总记录条数</span><br><span class="line">#<span class="built_in">count</span>(具体字段)统计具体字段不为<span class="keyword">null</span>的总条数</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>分组函数不能直接出现在where子句后面</p>
<p>因为group by在where后面执行</p>
<p>而分组函数在group by后面执行</p>
<p>分组函数如果没有group by语句 则自成一组</p>
<p>下面是sql关键词顺序</p>
<p>from  where group by having select order by</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">#找出工资高于平均工资的员工</span><br><span class="line">#第一步,找出平均工资,结果为<span class="number">2073</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp;</span><br><span class="line">#第二步，找出高于平均工资的员工</span><br><span class="line"><span class="keyword">select</span> ename ,sal <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="operator">&gt;</span><span class="number">2073</span>；</span><br><span class="line">#通过子查询 合一起</span><br><span class="line"><span class="keyword">select</span> ename,sal form emp <span class="keyword">where</span> sal <span class="operator">&gt;</span> (<span class="keyword">select</span> <span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp)；</span><br></pre></td></tr></table></figure>



<h4 id="8-ifnull"><a href="#8-ifnull" class="headerlink" title="8.ifnull"></a>8.ifnull</h4><blockquote>
<p>ifnull()函数 可以将null值转换成其他值</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#ifnull关键字用法(可能为<span class="keyword">null</span>的数据，当做什么处理)</span><br><span class="line"><span class="keyword">select</span> ename,(sal<span class="operator">+</span>ifnull(comm,<span class="number">0</span>))<span class="operator">*</span><span class="number">12</span> <span class="keyword">from</span> emp</span><br></pre></td></tr></table></figure>

<h4 id="9-分组查询"><a href="#9-分组查询" class="headerlink" title="9.分组查询"></a>9.分组查询</h4><blockquote>
<p>group by 关键字</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#找出每个岗位的最高薪资</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal),job <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> job;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>当用group by分组后 查询的字段只能为分组的字段</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">#可以联合分组</span><br><span class="line">#找出 每个部门 不同岗位 的最高薪资</span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">  deptno,job,<span class="built_in">max</span>(sal)</span><br><span class="line">  <span class="keyword">from</span></span><br><span class="line">   emp</span><br><span class="line">   <span class="keyword">group</span> <span class="keyword">by</span></span><br><span class="line">     deptno,</span><br></pre></td></tr></table></figure>

<h4 id="10-distinct"><a href="#10-distinct" class="headerlink" title="10.distinct"></a>10.distinct</h4><blockquote>
<p>去除重复记录</p>
<p>distinct只能出现在字段最前面，去除联合字段的重复记录</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#是 去重 deptno以及job的联合字段的数据</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> deptno job <span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>



<h3 id="3-连接查询"><a href="#3-连接查询" class="headerlink" title="3.连接查询"></a>3.连接查询</h3><h4 id="sql关键字顺序"><a href="#sql关键字顺序" class="headerlink" title="sql关键字顺序"></a>sql关键字顺序</h4><h5 id="from-where-group-by-having-select-order-by-limit"><a href="#from-where-group-by-having-select-order-by-limit" class="headerlink" title="from  where group by having select order by limit"></a>from  where group by having select order by limit</h5><blockquote>
<p>select from where group by having order by limit   </p>
</blockquote>
<h4 id="1-笛卡尔积"><a href="#1-笛卡尔积" class="headerlink" title="1.笛卡尔积"></a>1.笛卡尔积</h4><blockquote>
<p>当两条表进行连接查询的时候，没有条件进行限制，最终查询的结果是两条记录条数的乘积</p>
</blockquote>
<h5 id="如何避免笛卡尔积现象"><a href="#如何避免笛卡尔积现象" class="headerlink" title="如何避免笛卡尔积现象"></a>如何避免笛卡尔积现象</h5><blockquote>
<p>加条件进行过滤</p>
<p>注意！！！ 记录的匹配次数不会减少 只会显示有效记录 </p>
<p>没有提高效率</p>
</blockquote>
<h4 id="2-给表起别名"><a href="#2-给表起别名" class="headerlink" title="2.给表起别名"></a>2.给表起别名</h4><blockquote>
<p>有两个好处</p>
<p>1.可读性好</p>
<p>2.执行效率高，会具体到起别名的表的字段</p>
</blockquote>
<h4 id="3-join-on关键词"><a href="#3-join-on关键词" class="headerlink" title="3.join on关键词"></a>3.join on关键词</h4><h5 id="1-等值连接"><a href="#1-等值连接" class="headerlink" title="1.等值连接"></a>1.等值连接</h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">#SQL99语法</span><br><span class="line">##后面还可以加<span class="keyword">where</span>关键字 进行条件筛选</span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">  e.ename,d.dname</span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">  emp e</span><br><span class="line">(<span class="keyword">inner</span>) <span class="keyword">join</span></span><br><span class="line">  dept d</span><br><span class="line"><span class="keyword">on</span> </span><br><span class="line">   e.deptno<span class="operator">=</span> d.deptno;</span><br><span class="line">##等价于这条SQL92语法 已被淘汰</span><br><span class="line"><span class="keyword">select</span> e.name,d.dname <span class="keyword">from</span> emp e,dept d <span class="keyword">where</span> e.deptno <span class="operator">=</span> d.deptno</span><br></pre></td></tr></table></figure>

<h5 id="2-非等值连接"><a href="#2-非等值连接" class="headerlink" title="2.非等值连接"></a>2.非等值连接</h5><blockquote>
<p>找出每个员工的工资等级 显示员工名 工资 工资等级</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">  e.ename,e.salary,s.grade</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">  emp e</span><br><span class="line"><span class="keyword">join</span></span><br><span class="line">  salgrade s</span><br><span class="line"><span class="keyword">on</span></span><br><span class="line">  e.salary <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal;</span><br></pre></td></tr></table></figure>

<h5 id="3-自连接"><a href="#3-自连接" class="headerlink" title="3.自连接"></a>3.自连接</h5><blockquote>
<p>找出每个员工的上级领导，要求显示员工名以及领导名</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">   a.ename <span class="keyword">as</span> <span class="string">&#x27;员工名&#x27;</span>,b.ename <span class="keyword">as</span> <span class="string">&#x27;领导名&#x27;</span></span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">   emp a</span><br><span class="line"><span class="keyword">join</span></span><br><span class="line">   emp b</span><br><span class="line"><span class="keyword">on</span> a.mgr<span class="operator">=</span>b.empno   </span><br></pre></td></tr></table></figure>

<h5 id="4-外连接"><a href="#4-外连接" class="headerlink" title="4.外连接"></a>4.外连接</h5><blockquote>
<p>内连接： 假设A表和B表进行连接，使用内连接的话，凡是A表和B表匹配得上的记录都会查出来，AB两表没有主副之分</p>
</blockquote>
<blockquote>
<p>外连接： 假设A表和B表进行连接，使用外连接，AB两张表，有一张为主表，有一张为副标，主要查询主表数据，如果副表数据没有与主表数据匹配上 副表会自动模拟null匹配</p>
</blockquote>
<blockquote>
<p>外连接分为左外连接以及右外连接</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> a.ename <span class="string">&#x27;员工&#x27;</span>,b.ename <span class="string">&#x27;老板&#x27;</span></span><br><span class="line"><span class="keyword">from</span> emp a</span><br><span class="line">#<span class="keyword">join</span>前面为<span class="keyword">left</span>就为左外连接 为<span class="keyword">right</span>就为右外连接</span><br><span class="line"><span class="keyword">left</span>(<span class="keyword">right</span>) <span class="keyword">join</span> emp b</span><br><span class="line"><span class="keyword">on</span> a.mgr <span class="operator">=</span> b.empno;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>案例 找出哪个部门没有员工</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">   d.<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">   emp e</span><br><span class="line"><span class="keyword">right</span> <span class="keyword">join</span> </span><br><span class="line">   dept d</span><br><span class="line"><span class="keyword">on</span> </span><br><span class="line">   e.deptno <span class="operator">=</span> d.deptno</span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">   e.empno <span class="keyword">is</span> <span class="keyword">null</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h5 id="5-全连接"><a href="#5-全连接" class="headerlink" title="5.全连接"></a>5.全连接</h5><blockquote>
<p>暂时没学 以后补充</p>
</blockquote>
<h5 id="6-三张表连接"><a href="#6-三张表连接" class="headerlink" title="6.三张表连接"></a>6.三张表连接</h5><blockquote>
<p>表示A表和B表先连接 ，连接之后A表和C表在连接</p>
<p>案例 找出员工名字 部门名字 以及工资等级</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">   e.ename,d.dname,s.grade</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">   emp e</span><br><span class="line"><span class="keyword">join</span> </span><br><span class="line">   dname d</span><br><span class="line"><span class="keyword">on</span> </span><br><span class="line">   e.deptno <span class="operator">=</span> d.deptno</span><br><span class="line"><span class="keyword">join</span></span><br><span class="line">   salgrade s</span><br><span class="line"><span class="keyword">on</span></span><br><span class="line">   e.sal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>案例： 找出每一个员工的部门名称 工资等级 以及上级领导</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">   e.ename <span class="string">&#x27;员工&#x27;</span>,d.dname,s.grade,e1.ename <span class="string">&#x27;领导&#x27;</span></span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">   emp e</span><br><span class="line"><span class="keyword">join</span></span><br><span class="line">   dept d</span><br><span class="line"><span class="keyword">on</span> </span><br><span class="line">   e.deptno <span class="operator">=</span> d.deptno</span><br><span class="line"><span class="keyword">join</span> </span><br><span class="line">   salgrade s</span><br><span class="line"><span class="keyword">on</span> </span><br><span class="line">   e.sal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal</span><br><span class="line"><span class="keyword">left</span> <span class="keyword">join</span></span><br><span class="line">   ename e1</span><br><span class="line"><span class="keyword">on</span> </span><br><span class="line">   e.mgr <span class="operator">=</span> e1.empno;</span><br></pre></td></tr></table></figure>

<h4 id="4-嵌套子查询"><a href="#4-嵌套子查询" class="headerlink" title="4.嵌套子查询"></a>4.嵌套子查询</h4><h5 id="where后面嵌套子查询"><a href="#where后面嵌套子查询" class="headerlink" title="where后面嵌套子查询"></a>where后面嵌套子查询</h5><blockquote>
<p>案例： 找出薪资大于平均薪资的员工记录</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="operator">&gt;</span> (<span class="keyword">select</span> <span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp );</span><br></pre></td></tr></table></figure>

<h5 id="from后面嵌套子查询"><a href="#from后面嵌套子查询" class="headerlink" title="from后面嵌套子查询"></a>from后面嵌套子查询</h5><blockquote>
<p>案例：找出每个部门平均薪水的薪资等级</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">#第一步 找出每个部门平均薪水</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#第二步，把上面结果看做临时表，让临时表t和salgrade表结合</span><br><span class="line">#条件为t.<span class="built_in">avg</span>(sal) <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal</span><br><span class="line"><span class="keyword">select</span> </span><br><span class="line">   t.<span class="operator">*</span>,s.grade</span><br><span class="line"><span class="keyword">from</span> </span><br><span class="line">   (<span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">as</span> avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) <span class="keyword">as</span> t</span><br><span class="line"><span class="keyword">join</span></span><br><span class="line">   salgrade s</span><br><span class="line"><span class="keyword">on</span> </span><br><span class="line">   t.avgsal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal;</span><br></pre></td></tr></table></figure>

<h5 id="select后面嵌套子查询"><a href="#select后面嵌套子查询" class="headerlink" title="select后面嵌套子查询"></a>select后面嵌套子查询</h5><blockquote>
<p>案例： 找出每个员工所在的部门名称 </p>
<p>要求显示员工名和部门名</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> e.ename,d.dname</span><br><span class="line"><span class="keyword">from</span> emp e</span><br><span class="line"><span class="keyword">join</span> dept d</span><br><span class="line"><span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno</span><br><span class="line">#老师举了一个不常用的例子 以后再补充</span><br></pre></td></tr></table></figure>

<h4 id="5-union关键字"><a href="#5-union关键字" class="headerlink" title="5.union关键字"></a>5.union关键字</h4><blockquote>
<p>可以将查询集相加</p>
<p>select stuno,stu from class1</p>
<p>union</p>
<p>select stuno,stu from class2</p>
</blockquote>
<h4 id="6-limit"><a href="#6-limit" class="headerlink" title="6.limit"></a>6.limit</h4><blockquote>
<p>limit是mysql独有的 oracle有个相同机制叫rownumber</p>
<p>语法机制</p>
<p>limit startIndex，length</p>
<p>startIndex表示起始位置 从0开始 0表示第一条数据</p>
<p>length表示取几个</p>
<p>案例： 取出工资前五名的员工</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#取出所有员工 按工资降序排</span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span>;</span><br><span class="line">#取出员工工资前五</span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span> limit <span class="number">0</span>,<span class="number">5</span>;</span><br><span class="line">#如果直接写一个数字，默认起始索引为<span class="number">0</span></span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span> limit <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>分页 假设每一页显示三条记录</p>
<p>第一页 0,3</p>
<p>第二页 3,3</p>
<p>第三页 6,3</p>
<p>得出规律 </p>
<p>每页显示pageSize条记录</p>
<p>第pageNo页的索引为 （pageNo-1）*pageSize</p>
</blockquote>
<h2 id="4-DDL"><a href="#4-DDL" class="headerlink" title="4.DDL"></a>4.DDL</h2><h3 id="1-创建表"><a href="#1-创建表" class="headerlink" title="1.创建表"></a>1.创建表</h3><h4 id="mysql常用数据类型"><a href="#mysql常用数据类型" class="headerlink" title="mysql常用数据类型"></a>mysql常用数据类型</h4><ol>
<li><p>int 整数型</p>
</li>
<li><p>bigint 长整型（java中的long）</p>
</li>
<li><p>float 浮点型</p>
</li>
<li><p>char 定长字符串</p>
</li>
<li><p>varchar 可变长字符串（最大255）</p>
</li>
<li><p>date 日期类型</p>
</li>
<li><p>BLOB 二进制大对象（存储图片，视频等流媒体信息）</p>
</li>
<li><p>CLOB 字符大对象（存储较大文本，可存储4G文本）</p>
</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> t_student;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t_student(</span><br><span class="line">     <span class="keyword">no</span> <span class="type">bigint</span>,</span><br><span class="line">     name <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">     gender <span class="type">char</span>(<span class="number">1</span>) <span class="keyword">default</span> <span class="number">1</span>,</span><br><span class="line">     classno <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">     birth <span class="type">char</span>(<span class="number">10</span>)</span><br><span class="line"> );</span><br></pre></td></tr></table></figure>

<h4 id="复制表"><a href="#复制表" class="headerlink" title="复制表"></a>复制表</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> tmp1 <span class="keyword">as</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t_student</span><br></pre></td></tr></table></figure>

<h4 id="设置自增"><a href="#设置自增" class="headerlink" title="设置自增"></a>设置自增</h4><blockquote>
<p>auto_increment </p>
</blockquote>
<h4 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h4><h5 id="1-非空约束"><a href="#1-非空约束" class="headerlink" title="1.非空约束"></a>1.非空约束</h5><p>not null</p>
<p>约束的字段不能为null</p>
<blockquote>
<p>not null 没有表级约束 只有列级约束</p>
</blockquote>
<h5 id="2-唯一约束"><a href="#2-唯一约束" class="headerlink" title="2.唯一约束"></a>2.唯一约束</h5><p>unique</p>
<p>约束的字段不能重复</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#代表各自不能重复 列级约束</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t(</span><br><span class="line">id <span class="type">int</span> <span class="keyword">unique</span>,</span><br><span class="line">username <span class="type">varchar</span>(<span class="number">255</span>) <span class="keyword">unique</span></span><br><span class="line">)</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#代表联合不能重复 表级约束</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t(</span><br><span class="line">id <span class="type">int</span>,</span><br><span class="line">username <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line"><span class="keyword">unique</span>(id,username)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>



<h5 id="3-主键约束"><a href="#3-主键约束" class="headerlink" title="3.主键约束"></a>3.主键约束</h5><p>primary key</p>
<p>约束的字段不能为null也不能重复</p>
<blockquote>
<p>主键的作用：唯一标识</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t_user(</span><br><span class="line"> id <span class="type">int</span>,</span><br><span class="line"> username <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line"> <span class="keyword">primary</span> key(id)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>



<h5 id="4-外键约束"><a href="#4-外键约束" class="headerlink" title="4.外键约束"></a>4.外键约束</h5><p>foreign key</p>
<p>外键值可以为null</p>
<p>外键引用字段，必须唯一字段，不一定要是主键 </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> t_student;</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> t_class;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t_class(</span><br><span class="line">  cno <span class="type">int</span> <span class="keyword">primary</span> key,</span><br><span class="line">  cname <span class="type">varchar</span>(<span class="number">255</span>)</span><br><span class="line">);</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> t_student(</span><br><span class="line">  sno <span class="type">int</span> <span class="keyword">primary</span> key ,</span><br><span class="line">  sname <span class="type">varchar</span>(<span class="number">255</span>),</span><br><span class="line">  classno <span class="type">int</span>,</span><br><span class="line">  <span class="keyword">foreign</span> key(classno) <span class="keyword">references</span> t_class(cno)</span><br><span class="line">)</span><br></pre></td></tr></table></figure>



<h5 id="5-检查约束"><a href="#5-检查约束" class="headerlink" title="5.检查约束"></a>5.检查约束</h5><p>check</p>
<p>oracle有check约束 mysql没有</p>
<h2 id="5-DML"><a href="#5-DML" class="headerlink" title="5.DML"></a>5.DML</h2><h3 id="1-插入数据"><a href="#1-插入数据" class="headerlink" title="1.插入数据"></a>1.插入数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> t_student (<span class="keyword">no</span>,name,gender,classno,birth)</span><br><span class="line"><span class="keyword">values</span>(<span class="number">001</span>,<span class="string">&#x27;zhangsan&#x27;</span>,<span class="string">&#x27;1&#x27;</span>,<span class="string">&#x27;高三一班&#x27;</span>,<span class="string">&#x27;2001-11-23&#x27;</span>)</span><br></pre></td></tr></table></figure>

<h4 id="将查询结果插入一张表中"><a href="#将查询结果插入一张表中" class="headerlink" title="将查询结果插入一张表中"></a>将查询结果插入一张表中</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> tmp1  <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t_student</span><br></pre></td></tr></table></figure>

<h3 id="2-修改数据"><a href="#2-修改数据" class="headerlink" title="2.修改数据"></a>2.修改数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">#<span class="keyword">update</span>语句如果没有<span class="keyword">where</span>条件是将整张表更新</span><br><span class="line">#<span class="keyword">update</span>语句后面是逗号，不是<span class="keyword">and</span>！！！</span><br><span class="line"><span class="keyword">update</span> dept1 <span class="keyword">set</span> loc <span class="operator">=</span><span class="string">&#x27;shanghai&#x27;</span>,dname<span class="operator">=</span><span class="string">&#x27;zhangsan&#x27;</span> <span class="keyword">where</span> deptno<span class="operator">=</span><span class="number">10</span></span><br></pre></td></tr></table></figure>

<h3 id="3-删除数据"><a href="#3-删除数据" class="headerlink" title="3.删除数据"></a>3.删除数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#如果没有<span class="keyword">where</span>条件表示全部删除</span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> dept1 <span class="keyword">where</span> deptno <span class="operator">=</span><span class="number">10</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#怎么快速删除大表 不可恢复！！</span><br><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> emp1;</span><br></pre></td></tr></table></figure>

<h2 id="6-事务"><a href="#6-事务" class="headerlink" title="6.事务"></a>6.事务</h2><blockquote>
<p>一个事务是一个完整的业务逻辑单元，不可再分</p>
<p>与事务相关的语句只有：DML语句</p>
<p>事物的存在是为了保证数据的完整性，安全性</p>
</blockquote>
<h3 id="事物的特性"><a href="#事物的特性" class="headerlink" title="事物的特性"></a>事物的特性</h3><blockquote>
<p>事务包括四大特性： ACID</p>
<p>A： 原子性：事务是最小的工作单元不可再分</p>
<p>C： 一致性：事务必须保证多条DML语句同时成功或者同时失败</p>
<p>I:    隔离性：事务A与事务B之间具有隔离</p>
<p>D:  持久性：持久性说的是最终数据必须持久化到硬盘文化</p>
</blockquote>
<h4 id="关于事物的隔离性"><a href="#关于事物的隔离性" class="headerlink" title="关于事物的隔离性"></a>关于事物的隔离性</h4><p>第一级别：读未提交</p>
<p>第二级别：读已提交</p>
<p>第三级别：可重复读</p>
<p>第四级别：序列化读，串行化读</p>
<h2 id="7-数据库三大范式"><a href="#7-数据库三大范式" class="headerlink" title="7.数据库三大范式"></a>7.数据库三大范式</h2><h3 id="第一范式"><a href="#第一范式" class="headerlink" title="第一范式"></a>第一范式</h3><p>任何一张表都应有主键，且每一个字段原子性不可再分</p>
<h3 id="第二范式"><a href="#第二范式" class="headerlink" title="第二范式"></a>第二范式</h3><p>所有非主键字段完全依赖主键，不能产生部分依赖</p>
<blockquote>
<p>解决方式 多对多？三张表，关系表两个外键</p>
</blockquote>
<h3 id="第三范式"><a href="#第三范式" class="headerlink" title="第三范式"></a>第三范式</h3><p>在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖</p>
<blockquote>
<p>一对多?  两张表 多的表加外键</p>
</blockquote>
<h3 id="在实际的开发中，以客户的需求为主，有时候会以冗余换执行速度"><a href="#在实际的开发中，以客户的需求为主，有时候会以冗余换执行速度" class="headerlink" title="在实际的开发中，以客户的需求为主，有时候会以冗余换执行速度"></a>在实际的开发中，以客户的需求为主，有时候会以冗余换执行速度</h3><blockquote>
<p>一对一？ 主键共享 或者 外键唯一</p>
</blockquote>
<h2 id="经典易错题"><a href="#经典易错题" class="headerlink" title="经典易错题"></a>经典易错题</h2><h3 id="1-找出每个部门的最高薪资，显示薪资大于2900的数据"><a href="#1-找出每个部门的最高薪资，显示薪资大于2900的数据" class="headerlink" title="1.找出每个部门的最高薪资，显示薪资大于2900的数据"></a>1.找出每个部门的最高薪资，显示薪资大于2900的数据</h3><blockquote>
<p>常见效率低的解决方式：</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#可以分两步</span><br><span class="line">#第一步，找出每个部门的最高薪资</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal),deptno <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#第二步，用<span class="keyword">having</span>过滤大于<span class="number">2900</span>的数据</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal),deptno <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno <span class="keyword">having</span> <span class="built_in">max</span>(sal) <span class="operator">&gt;</span> <span class="number">2900</span>;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>正确解决方式：</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#第一步，找出每个部门的最高薪资</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal),deptno <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#第二步，先用<span class="keyword">where</span>排除掉<span class="number">2900</span>以下的数据，再分组查询</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal),deptno <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="operator">&gt;</span> <span class="number">2900</span> <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br></pre></td></tr></table></figure>

<h3 id="2-（举个只能用having不能用where的例子）找出每个部门的平均薪资，要求显示薪资大于2000的数据"><a href="#2-（举个只能用having不能用where的例子）找出每个部门的平均薪资，要求显示薪资大于2000的数据" class="headerlink" title="2.（举个只能用having不能用where的例子）找出每个部门的平均薪资，要求显示薪资大于2000的数据"></a>2.（举个只能用having不能用where的例子）找出每个部门的平均薪资，要求显示薪资大于2000的数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno <span class="keyword">having</span> <span class="built_in">avg</span>(sal) <span class="operator">&gt;</span> <span class="number">2000</span>;</span><br></pre></td></tr></table></figure>

<h3 id="3-统计岗位的数量"><a href="#3-统计岗位的数量" class="headerlink" title="3.统计岗位的数量"></a>3.统计岗位的数量</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">count</span> (<span class="keyword">distinct</span> job) <span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>

<h3 id="4-取得每个部门最高薪水的人员名称"><a href="#4-取得每个部门最高薪水的人员名称" class="headerlink" title="4.取得每个部门最高薪水的人员名称"></a>4.取得每个部门最高薪水的人员名称</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#第一步查询每个部门最高的薪水</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">max</span>(sal) maxsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#第二步 将以上当成t表 t表和emp e表进行连接</span><br><span class="line">#条件为 t.deptno <span class="operator">=</span>e.deptno t.maxsal <span class="operator">=</span>e.sal</span><br><span class="line"><span class="keyword">select</span> e.ename,t.<span class="operator">*</span> <span class="keyword">from</span> (<span class="keyword">select</span> deptno,<span class="built_in">max</span>(sal) maxsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) t <span class="keyword">join</span> emp e <span class="keyword">on</span> t.deptno <span class="operator">=</span> e.deptno <span class="keyword">and</span> t.maxsal <span class="operator">=</span> e.sal;</span><br></pre></td></tr></table></figure>

<h3 id="5-哪些人的薪资在部门的平均薪水之上"><a href="#5-哪些人的薪资在部门的平均薪水之上" class="headerlink" title="5.哪些人的薪资在部门的平均薪水之上"></a>5.哪些人的薪资在部门的平均薪水之上</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#先查出每个部门的平均薪水</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#将上表命名为t t表和emp e表进行连接</span><br><span class="line">#连接条件 t.deptno <span class="operator">=</span> e.deptno t.avgsal<span class="operator">&lt;</span>e.sal</span><br><span class="line"><span class="keyword">select</span> e.ename,e.deptno,e.sal <span class="keyword">from</span> (<span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) t <span class="keyword">join</span> emp e <span class="keyword">on</span> t.deptno <span class="operator">=</span> e.deptno <span class="keyword">and</span> t.avgsal<span class="operator">&lt;</span> e.sal;</span><br></pre></td></tr></table></figure>

<h3 id="6-取得部门中所有人的平均的薪水等级"><a href="#6-取得部门中所有人的平均的薪水等级" class="headerlink" title="6.取得部门中所有人的平均的薪水等级"></a>6.取得部门中所有人的平均的薪水等级</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#先将emp表 e 与  salgrade表 s进行连接</span><br><span class="line">#连接条件为 e.sal beween losal <span class="keyword">and</span> hisal</span><br><span class="line"><span class="keyword">select</span> e.ename,e.sal,e.deptno,s.grade <span class="keyword">from</span> emp e </span><br><span class="line"><span class="keyword">join</span> salgrade s  <span class="keyword">on</span> e.sal <span class="keyword">between</span> losal <span class="keyword">and</span> hisal;</span><br><span class="line">#在基于以上结果按照deptno分组 求grade平均值</span><br><span class="line"><span class="keyword">select</span> e.deptno,<span class="built_in">avg</span>(s.grade) <span class="keyword">from</span> emp e <span class="keyword">join</span> salgrade s <span class="keyword">on</span> e.sal <span class="keyword">between</span> losal <span class="keyword">and</span> hisal <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br></pre></td></tr></table></figure>

<h3 id="7-不用分组函数-max-，取得最高薪水"><a href="#7-不用分组函数-max-，取得最高薪水" class="headerlink" title="7.不用分组函数(max)，取得最高薪水"></a>7.不用分组函数(max)，取得最高薪水</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#考虑到不能用max函数，可以使用<span class="keyword">desc</span>降序排列，再通过limit取第一条记录</span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span> limit <span class="number">1</span>;</span><br><span class="line">#答案上有提到有另一种方式 表的自连接</span><br><span class="line"><span class="keyword">select</span> sal <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> <span class="keyword">distinct</span> a.sal <span class="keyword">from</span> emp a <span class="keyword">join</span> emp b <span class="keyword">on</span> a.sal <span class="operator">&lt;</span> b.sal);</span><br></pre></td></tr></table></figure>

<h3 id="8-取得平均薪水最高的部门的部门编号"><a href="#8-取得平均薪水最高的部门的部门编号" class="headerlink" title="8.取得平均薪水最高的部门的部门编号"></a>8.取得平均薪水最高的部门的部门编号</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#获取每个部门平均薪水</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#降序排选第一个</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno <span class="keyword">order</span> <span class="keyword">by</span> avgsal <span class="keyword">desc</span> limit <span class="number">1</span>;</span><br><span class="line">#答案提供了第二种方式通过max进行获取</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(t.avgsal) <span class="keyword">from</span> (<span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) t ;</span><br></pre></td></tr></table></figure>

<h3 id="9-取得平均薪水最高的部门的部门的名称"><a href="#9-取得平均薪水最高的部门的部门的名称" class="headerlink" title="9.取得平均薪水最高的部门的部门的名称"></a>9.取得平均薪水最高的部门的部门的名称</h3><blockquote>
<p>下面这个是错误示范 凡事别先想临时表解决！ 复杂化了</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#获取每个部门的部门编号以及平均薪水</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#与部门表d进行连接 连接条件d.deptno <span class="operator">=</span>t.deptno</span><br><span class="line">#在<span class="keyword">desc</span>降序排序 取limit一条记录</span><br><span class="line"><span class="keyword">select</span> d.dname,t.deptno,t.avgsal <span class="keyword">from</span> (<span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) t <span class="keyword">join</span> dept d <span class="keyword">on</span> d.deptno<span class="operator">=</span>t.deptno <span class="keyword">order</span> <span class="keyword">by</span> t.avgsal <span class="keyword">desc</span> limit <span class="number">1</span> ;</span><br></pre></td></tr></table></figure>

<blockquote>
<p>正确示范</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#获取每个部门的部门编号以及平均薪水</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#将结果倒序，取第一条记录</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno <span class="keyword">order</span> <span class="keyword">by</span> avgsal <span class="keyword">desc</span> limit <span class="number">1</span>;</span><br><span class="line">#自连接dept表 连接条件deptno相等 得到dept名字</span><br><span class="line"><span class="keyword">select</span> e.deptno,d.dname,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp e <span class="keyword">join</span> dept d <span class="keyword">on</span> e.deptno <span class="operator">=</span> d.deptno <span class="keyword">group</span> <span class="keyword">by</span> d.deptno <span class="keyword">order</span> <span class="keyword">by</span> avgsal <span class="keyword">desc</span> limit <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h3 id="10-求平均薪水的等级最低的部门名称"><a href="#10-求平均薪水的等级最低的部门名称" class="headerlink" title="10.求平均薪水的等级最低的部门名称"></a>10.求平均薪水的等级最低的部门名称</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">#求每个部门的平均薪水</span><br><span class="line"><span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno;</span><br><span class="line">#根据上面结果 自连接salgrade表s </span><br><span class="line">#条件t.avgsal beween s.losal <span class="keyword">and</span> s.hisal</span><br><span class="line"><span class="keyword">select</span> t.deptno,s.grade <span class="keyword">from</span> (<span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) t <span class="keyword">join</span> salgrade s <span class="keyword">on</span> t.avgsal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal;</span><br><span class="line">#发现没部门名称，再<span class="keyword">join</span>连接dept表d 条件deptnp相等</span><br><span class="line">#最后升序排列取第一行</span><br><span class="line"><span class="keyword">select</span> t.deptno,d.dname,s.grade <span class="keyword">from</span> (<span class="keyword">select</span> deptno,<span class="built_in">avg</span>(sal) avgsal <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> deptno) t <span class="keyword">join</span> salgrade s <span class="keyword">on</span> t.avgsal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal <span class="keyword">join</span> dept d <span class="keyword">on</span> t.deptno <span class="operator">=</span>d.deptno <span class="keyword">order</span> <span class="keyword">by</span> s.grade <span class="keyword">asc</span> limit <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h3 id="11-取得比普通员工的最高薪水还要高的领导人姓名"><a href="#11-取得比普通员工的最高薪水还要高的领导人姓名" class="headerlink" title="11.取得比普通员工的最高薪水还要高的领导人姓名"></a>11.取得比普通员工的最高薪水还要高的领导人姓名</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">#取得领导人名单</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> mgr <span class="keyword">from</span> emp <span class="keyword">where</span> mgr <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span>;</span><br><span class="line">#取得所有员工除去领导以外的最高薪水值</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(sal) <span class="keyword">from</span> emp <span class="keyword">where</span> empno <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> <span class="keyword">distinct</span> mgr <span class="keyword">from</span> emp <span class="keyword">where</span> mgr <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span>);</span><br><span class="line">#找出高于<span class="number">1600</span>的</span><br><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">where</span> sal <span class="operator">&gt;</span>(<span class="keyword">select</span> <span class="built_in">max</span>(sal) <span class="keyword">from</span> emp <span class="keyword">where</span> empno <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> <span class="keyword">distinct</span> mgr <span class="keyword">from</span> emp <span class="keyword">where</span> mgr <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span>));</span><br></pre></td></tr></table></figure>

<h3 id="12-取得薪水最高的前五名员工"><a href="#12-取得薪水最高的前五名员工" class="headerlink" title="12.取得薪水最高的前五名员工"></a>12.取得薪水最高的前五名员工</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span> limit <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<h3 id="13-取得薪水最高的第六到第十名员工"><a href="#13-取得薪水最高的第六到第十名员工" class="headerlink" title="13.取得薪水最高的第六到第十名员工"></a>13.取得薪水最高的第六到第十名员工</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> ename,sal <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> sal <span class="keyword">desc</span> limit <span class="number">5</span>,<span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<h3 id="14-取得最后入职的5名员工"><a href="#14-取得最后入职的5名员工" class="headerlink" title="14.取得最后入职的5名员工"></a>14.取得最后入职的5名员工</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> ename,hiredate <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> hiredate <span class="keyword">desc</span> limit <span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<h3 id="15-取得每个薪水等级有多少员工"><a href="#15-取得每个薪水等级有多少员工" class="headerlink" title="15.取得每个薪水等级有多少员工"></a>15.取得每个薪水等级有多少员工</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#找出每个员工的薪水等级</span><br><span class="line"><span class="keyword">select</span> e.ename,s.grade <span class="keyword">from</span> emp e <span class="keyword">join</span> salgrade s <span class="keyword">on</span> e.sal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal;</span><br><span class="line">#分组进行count</span><br><span class="line"><span class="keyword">select</span> s.grade,<span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">from</span> emp e <span class="keyword">join</span> salgrade s <span class="keyword">on</span> e.sal <span class="keyword">between</span> s.losal <span class="keyword">and</span> s.hisal <span class="keyword">group</span> <span class="keyword">by</span> s.grade;</span><br></pre></td></tr></table></figure>

<h3 id="16-面试题"><a href="#16-面试题" class="headerlink" title="16.面试题"></a>16.面试题</h3><blockquote>
<p>13、面试题：<br>有 3 个表 S(学生表)，C（课程表），SC（学生选课表）<br>S（SNO，SNAME）代表（学号，姓名）<br>C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）<br>SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）<br>问题：<br>1，找出没选过“黎明”老师的所有学生姓名。<br>2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。<br>3，即学过 1 号课程又学过 2 号课所有学生的姓名。</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">#第<span class="number">1</span>小问</span><br><span class="line">#首先找出黎明老师的课程号</span><br><span class="line"><span class="keyword">select</span> cno <span class="keyword">from</span> c <span class="keyword">where</span> cteacher <span class="operator">=</span> <span class="string">&#x27;黎明&#x27;</span>;</span><br><span class="line">#找出没有选黎明老师课程号的学号</span><br><span class="line"><span class="keyword">select</span> sno <span class="keyword">from</span> sc <span class="keyword">where</span> cno <span class="keyword">not</span> <span class="keyword">in</span>(selct cno <span class="keyword">from</span> c <span class="keyword">where</span> cteacher<span class="operator">=</span><span class="string">&#x27;黎明&#x27;</span>);</span><br><span class="line">#自连接s表 查询姓名</span><br><span class="line"><span class="keyword">select</span> sname <span class="keyword">from</span> s <span class="keyword">join</span> (<span class="keyword">select</span> sno <span class="keyword">from</span> sc <span class="keyword">where</span> cno <span class="keyword">not</span> <span class="keyword">in</span>(<span class="keyword">select</span> cno <span class="keyword">from</span> c <span class="keyword">where</span> cteacher<span class="operator">=</span><span class="string">&#x27;黎明&#x27;</span>)) t <span class="keyword">on</span> s.sno<span class="operator">=</span>t.sno;</span><br><span class="line"></span><br><span class="line">#第二小问</span><br><span class="line"></span><br></pre></td></tr></table></figure>

</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta"><i class="fas fa-circle-user fa-fw"></i>文章作者: </span><span class="post-copyright-info"><a href="https://52zja.top">zjaaa</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta"><i class="fas fa-square-arrow-up-right fa-fw"></i>文章链接: </span><span class="post-copyright-info"><a href="https://52zja.top/2024/02/06/sql%E5%AD%A6%E4%B9%A0/">https://52zja.top/2024/02/06/sql%E5%AD%A6%E4%B9%A0/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta"><i class="fas fa-circle-exclamation fa-fw"></i>版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://52zja.top" target="_blank">云起</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a><a class="post-meta__tags" href="/tags/sql/">sql</a></div><div class="post_share"><div class="social-share" data-image="/img/icon.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1.1.3/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1.1.3/sharejs/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2024/02/08/python%E5%AD%A6%E4%B9%A0/" title="python学习"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">python学习</div></div></a></div><div class="next-post pull-right"><a href="/2024/01/31/mybatis%E7%9A%84%E4%BD%BF%E7%94%A8/" title="mybatis的使用"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">mybatis的使用</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><div><a href="/2024/02/10/ER%E5%9B%BE/" title="ER图"><div class="cover" style="background: var(--default-bg-color)"></div><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2024-02-10</div><div class="title">ER图</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src= "" data-lazy-src="/img/icon.jpg" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">zjaaa</div><div class="author-info__description">分享知识，认识世界</div></div><div class="card-info-data site-data is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">10</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">19</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">3</div></a></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/44528zja"><i class="fab fa-github"></i><span>Follow Me</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://github.com/44528zja" target="_blank" title="Github"><i class="fab fa-github" style="color: #24292e;"></i></a><a class="social-icon" href="https://github.com/44528zja" target="_blank" title="Email"><i class="fas fa-envelope" style="color: #4a7dbe;"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>公告</span></div><div class="announcement_content">欢迎来到我的碎碎念与生活杂记</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#Sql%E8%AF%AD%E5%8F%A5%E5%AD%A6%E4%B9%A0"><span class="toc-number">1.</span> <span class="toc-text">Sql语句学习</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%80-Sql%E6%95%B0%E6%8D%AE%E5%88%86%E7%B1%BB"><span class="toc-number">1.1.</span> <span class="toc-text">一.Sql数据分类</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-DQL-%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2%E8%AF%AD%E8%A8%80"><span class="toc-number">1.1.1.</span> <span class="toc-text">1.DQL(数据查询语言)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-DML-%E6%95%B0%E6%8D%AE%E6%93%8D%E4%BD%9C%E8%AF%AD%E8%A8%80"><span class="toc-number">1.1.2.</span> <span class="toc-text">2.DML(数据操作语言)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-DDL-%E6%95%B0%E6%8D%AE%E5%AE%9A%E4%B9%89%E8%AF%AD%E8%A8%80"><span class="toc-number">1.1.3.</span> <span class="toc-text">3.DDL(数据定义语言)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-TCL-%E4%BA%8B%E5%8A%A1%E6%8E%A7%E5%88%B6%E8%AF%AD%E8%A8%80"><span class="toc-number">1.1.4.</span> <span class="toc-text">4.TCL(事务控制语言)</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-DCL%EF%BC%88%E6%95%B0%E6%8D%AE%E6%8E%A7%E5%88%B6%E8%AF%AD%E8%A8%80%EF%BC%89"><span class="toc-number">1.1.5.</span> <span class="toc-text">5.DCL（数据控制语言）</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8C-mysql%E7%8B%AC%E7%89%B9%E5%91%BD%E4%BB%A4"><span class="toc-number">1.2.</span> <span class="toc-text">二.mysql独特命令</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%9F%A5%E7%9C%8B%E6%9C%89%E5%93%AA%E4%BA%9B%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.2.1.</span> <span class="toc-text">1.查看有哪些数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%88%9B%E5%BB%BA%E5%B1%9E%E4%BA%8E%E8%87%AA%E5%B7%B1%E7%9A%84%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.2.2.</span> <span class="toc-text">2.创建属于自己的数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E4%BD%BF%E7%94%A8%E8%87%AA%E5%B7%B1%E7%9A%84%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.2.3.</span> <span class="toc-text">3.使用自己的数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E6%9F%A5%E7%9C%8B%E5%BD%93%E5%89%8D%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%89%E5%93%AA%E4%BA%9B%E8%A1%A8"><span class="toc-number">1.2.4.</span> <span class="toc-text">4.查看当前数据库有哪些表</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E5%AF%BC%E5%85%A5sql%E8%84%9A%E6%9C%AC"><span class="toc-number">1.2.5.</span> <span class="toc-text">5.导入sql脚本</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.2.6.</span> <span class="toc-text">6.删除数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-%E6%9F%A5%E8%AF%A2%E8%A1%A8%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84"><span class="toc-number">1.2.7.</span> <span class="toc-text">7.查询表数据结构</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#8-%E6%9F%A5%E7%9C%8B%E5%BD%93%E5%89%8D%E4%BD%BF%E7%94%A8%E7%9A%84%E6%98%AF%E5%93%AA%E4%B8%AA%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.2.8.</span> <span class="toc-text">8.查看当前使用的是哪个数据库</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#9-%E7%BB%88%E6%AD%A2%E4%B8%80%E6%9D%A1%E8%AF%AD%E5%8F%A5%E5%91%BD%E4%BB%A4"><span class="toc-number">1.2.9.</span> <span class="toc-text">9.终止一条语句命令</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#10-%E5%AF%BC%E5%87%BA%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">1.2.10.</span> <span class="toc-text">10.导出数据库</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%B8%89-%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5%E8%AF%A6%E8%A7%A3"><span class="toc-number">1.3.</span> <span class="toc-text">三.查询语句详解</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-sql%E8%A7%84%E5%88%99"><span class="toc-number">1.3.1.</span> <span class="toc-text">1.sql规则</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-select%E5%85%B3%E9%94%AE%E5%AD%97"><span class="toc-number">1.3.2.</span> <span class="toc-text">2.select关键字</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#1-null"><span class="toc-number">1.3.2.1.</span> <span class="toc-text">1.null</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#2-between-%E2%80%A6-and-%E2%80%A6"><span class="toc-number">1.3.2.2.</span> <span class="toc-text">2.between … and …</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-and-%E4%BB%A5%E5%8F%8A-or"><span class="toc-number">1.3.2.3.</span> <span class="toc-text">3.and 以及 or</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-in"><span class="toc-number">1.3.2.4.</span> <span class="toc-text">4.in</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#5-like"><span class="toc-number">1.3.2.5.</span> <span class="toc-text">5.like</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#6-order-by"><span class="toc-number">1.3.2.6.</span> <span class="toc-text">6.order by</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#7-%E5%88%86%E7%BB%84%E5%87%BD%E6%95%B0"><span class="toc-number">1.3.2.7.</span> <span class="toc-text">7.分组函数</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#8-ifnull"><span class="toc-number">1.3.2.8.</span> <span class="toc-text">8.ifnull</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#9-%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.3.2.9.</span> <span class="toc-text">9.分组查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#10-distinct"><span class="toc-number">1.3.2.10.</span> <span class="toc-text">10.distinct</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E8%BF%9E%E6%8E%A5%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.3.3.</span> <span class="toc-text">3.连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#sql%E5%85%B3%E9%94%AE%E5%AD%97%E9%A1%BA%E5%BA%8F"><span class="toc-number">1.3.3.1.</span> <span class="toc-text">sql关键字顺序</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#from-where-group-by-having-select-order-by-limit"><span class="toc-number">1.3.3.1.1.</span> <span class="toc-text">from  where group by having select order by limit</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#1-%E7%AC%9B%E5%8D%A1%E5%B0%94%E7%A7%AF"><span class="toc-number">1.3.3.2.</span> <span class="toc-text">1.笛卡尔积</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#%E5%A6%82%E4%BD%95%E9%81%BF%E5%85%8D%E7%AC%9B%E5%8D%A1%E5%B0%94%E7%A7%AF%E7%8E%B0%E8%B1%A1"><span class="toc-number">1.3.3.2.1.</span> <span class="toc-text">如何避免笛卡尔积现象</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#2-%E7%BB%99%E8%A1%A8%E8%B5%B7%E5%88%AB%E5%90%8D"><span class="toc-number">1.3.3.3.</span> <span class="toc-text">2.给表起别名</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-join-on%E5%85%B3%E9%94%AE%E8%AF%8D"><span class="toc-number">1.3.3.4.</span> <span class="toc-text">3.join on关键词</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#1-%E7%AD%89%E5%80%BC%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.3.3.4.1.</span> <span class="toc-text">1.等值连接</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#2-%E9%9D%9E%E7%AD%89%E5%80%BC%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.3.3.4.2.</span> <span class="toc-text">2.非等值连接</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#3-%E8%87%AA%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.3.3.4.3.</span> <span class="toc-text">3.自连接</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#4-%E5%A4%96%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.3.3.4.4.</span> <span class="toc-text">4.外连接</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#5-%E5%85%A8%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.3.3.4.5.</span> <span class="toc-text">5.全连接</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#6-%E4%B8%89%E5%BC%A0%E8%A1%A8%E8%BF%9E%E6%8E%A5"><span class="toc-number">1.3.3.4.6.</span> <span class="toc-text">6.三张表连接</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-%E5%B5%8C%E5%A5%97%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.3.3.5.</span> <span class="toc-text">4.嵌套子查询</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#where%E5%90%8E%E9%9D%A2%E5%B5%8C%E5%A5%97%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.3.3.5.1.</span> <span class="toc-text">where后面嵌套子查询</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#from%E5%90%8E%E9%9D%A2%E5%B5%8C%E5%A5%97%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.3.3.5.2.</span> <span class="toc-text">from后面嵌套子查询</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#select%E5%90%8E%E9%9D%A2%E5%B5%8C%E5%A5%97%E5%AD%90%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.3.3.5.3.</span> <span class="toc-text">select后面嵌套子查询</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link" href="#5-union%E5%85%B3%E9%94%AE%E5%AD%97"><span class="toc-number">1.3.3.6.</span> <span class="toc-text">5.union关键字</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#6-limit"><span class="toc-number">1.3.3.7.</span> <span class="toc-text">6.limit</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#4-DDL"><span class="toc-number">1.4.</span> <span class="toc-text">4.DDL</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E5%88%9B%E5%BB%BA%E8%A1%A8"><span class="toc-number">1.4.1.</span> <span class="toc-text">1.创建表</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#mysql%E5%B8%B8%E7%94%A8%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="toc-number">1.4.1.1.</span> <span class="toc-text">mysql常用数据类型</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%A4%8D%E5%88%B6%E8%A1%A8"><span class="toc-number">1.4.1.2.</span> <span class="toc-text">复制表</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E8%AE%BE%E7%BD%AE%E8%87%AA%E5%A2%9E"><span class="toc-number">1.4.1.3.</span> <span class="toc-text">设置自增</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.4.1.4.</span> <span class="toc-text">约束</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link" href="#1-%E9%9D%9E%E7%A9%BA%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.4.1.4.1.</span> <span class="toc-text">1.非空约束</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#2-%E5%94%AF%E4%B8%80%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.4.1.4.2.</span> <span class="toc-text">2.唯一约束</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#3-%E4%B8%BB%E9%94%AE%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.4.1.4.3.</span> <span class="toc-text">3.主键约束</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#4-%E5%A4%96%E9%94%AE%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.4.1.4.4.</span> <span class="toc-text">4.外键约束</span></a></li><li class="toc-item toc-level-5"><a class="toc-link" href="#5-%E6%A3%80%E6%9F%A5%E7%BA%A6%E6%9D%9F"><span class="toc-number">1.4.1.4.5.</span> <span class="toc-text">5.检查约束</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-DML"><span class="toc-number">1.5.</span> <span class="toc-text">5.DML</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%8F%92%E5%85%A5%E6%95%B0%E6%8D%AE"><span class="toc-number">1.5.1.</span> <span class="toc-text">1.插入数据</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%B0%86%E6%9F%A5%E8%AF%A2%E7%BB%93%E6%9E%9C%E6%8F%92%E5%85%A5%E4%B8%80%E5%BC%A0%E8%A1%A8%E4%B8%AD"><span class="toc-number">1.5.1.1.</span> <span class="toc-text">将查询结果插入一张表中</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE"><span class="toc-number">1.5.2.</span> <span class="toc-text">2.修改数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE"><span class="toc-number">1.5.3.</span> <span class="toc-text">3.删除数据</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#6-%E4%BA%8B%E5%8A%A1"><span class="toc-number">1.6.</span> <span class="toc-text">6.事务</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8B%E7%89%A9%E7%9A%84%E7%89%B9%E6%80%A7"><span class="toc-number">1.6.1.</span> <span class="toc-text">事物的特性</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#%E5%85%B3%E4%BA%8E%E4%BA%8B%E7%89%A9%E7%9A%84%E9%9A%94%E7%A6%BB%E6%80%A7"><span class="toc-number">1.6.1.1.</span> <span class="toc-text">关于事物的隔离性</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#7-%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%89%E5%A4%A7%E8%8C%83%E5%BC%8F"><span class="toc-number">1.7.</span> <span class="toc-text">7.数据库三大范式</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%AC%AC%E4%B8%80%E8%8C%83%E5%BC%8F"><span class="toc-number">1.7.1.</span> <span class="toc-text">第一范式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%AC%AC%E4%BA%8C%E8%8C%83%E5%BC%8F"><span class="toc-number">1.7.2.</span> <span class="toc-text">第二范式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%AC%AC%E4%B8%89%E8%8C%83%E5%BC%8F"><span class="toc-number">1.7.3.</span> <span class="toc-text">第三范式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E5%9C%A8%E5%AE%9E%E9%99%85%E7%9A%84%E5%BC%80%E5%8F%91%E4%B8%AD%EF%BC%8C%E4%BB%A5%E5%AE%A2%E6%88%B7%E7%9A%84%E9%9C%80%E6%B1%82%E4%B8%BA%E4%B8%BB%EF%BC%8C%E6%9C%89%E6%97%B6%E5%80%99%E4%BC%9A%E4%BB%A5%E5%86%97%E4%BD%99%E6%8D%A2%E6%89%A7%E8%A1%8C%E9%80%9F%E5%BA%A6"><span class="toc-number">1.7.4.</span> <span class="toc-text">在实际的开发中，以客户的需求为主，有时候会以冗余换执行速度</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%BB%8F%E5%85%B8%E6%98%93%E9%94%99%E9%A2%98"><span class="toc-number">1.8.</span> <span class="toc-text">经典易错题</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%89%BE%E5%87%BA%E6%AF%8F%E4%B8%AA%E9%83%A8%E9%97%A8%E7%9A%84%E6%9C%80%E9%AB%98%E8%96%AA%E8%B5%84%EF%BC%8C%E6%98%BE%E7%A4%BA%E8%96%AA%E8%B5%84%E5%A4%A7%E4%BA%8E2900%E7%9A%84%E6%95%B0%E6%8D%AE"><span class="toc-number">1.8.1.</span> <span class="toc-text">1.找出每个部门的最高薪资，显示薪资大于2900的数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%EF%BC%88%E4%B8%BE%E4%B8%AA%E5%8F%AA%E8%83%BD%E7%94%A8having%E4%B8%8D%E8%83%BD%E7%94%A8where%E7%9A%84%E4%BE%8B%E5%AD%90%EF%BC%89%E6%89%BE%E5%87%BA%E6%AF%8F%E4%B8%AA%E9%83%A8%E9%97%A8%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E8%B5%84%EF%BC%8C%E8%A6%81%E6%B1%82%E6%98%BE%E7%A4%BA%E8%96%AA%E8%B5%84%E5%A4%A7%E4%BA%8E2000%E7%9A%84%E6%95%B0%E6%8D%AE"><span class="toc-number">1.8.2.</span> <span class="toc-text">2.（举个只能用having不能用where的例子）找出每个部门的平均薪资，要求显示薪资大于2000的数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E7%BB%9F%E8%AE%A1%E5%B2%97%E4%BD%8D%E7%9A%84%E6%95%B0%E9%87%8F"><span class="toc-number">1.8.3.</span> <span class="toc-text">3.统计岗位的数量</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E5%8F%96%E5%BE%97%E6%AF%8F%E4%B8%AA%E9%83%A8%E9%97%A8%E6%9C%80%E9%AB%98%E8%96%AA%E6%B0%B4%E7%9A%84%E4%BA%BA%E5%91%98%E5%90%8D%E7%A7%B0"><span class="toc-number">1.8.4.</span> <span class="toc-text">4.取得每个部门最高薪水的人员名称</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E5%93%AA%E4%BA%9B%E4%BA%BA%E7%9A%84%E8%96%AA%E8%B5%84%E5%9C%A8%E9%83%A8%E9%97%A8%E7%9A%84%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E4%B9%8B%E4%B8%8A"><span class="toc-number">1.8.5.</span> <span class="toc-text">5.哪些人的薪资在部门的平均薪水之上</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E5%8F%96%E5%BE%97%E9%83%A8%E9%97%A8%E4%B8%AD%E6%89%80%E6%9C%89%E4%BA%BA%E7%9A%84%E5%B9%B3%E5%9D%87%E7%9A%84%E8%96%AA%E6%B0%B4%E7%AD%89%E7%BA%A7"><span class="toc-number">1.8.6.</span> <span class="toc-text">6.取得部门中所有人的平均的薪水等级</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-%E4%B8%8D%E7%94%A8%E5%88%86%E7%BB%84%E5%87%BD%E6%95%B0-max-%EF%BC%8C%E5%8F%96%E5%BE%97%E6%9C%80%E9%AB%98%E8%96%AA%E6%B0%B4"><span class="toc-number">1.8.7.</span> <span class="toc-text">7.不用分组函数(max)，取得最高薪水</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#8-%E5%8F%96%E5%BE%97%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%9C%80%E9%AB%98%E7%9A%84%E9%83%A8%E9%97%A8%E7%9A%84%E9%83%A8%E9%97%A8%E7%BC%96%E5%8F%B7"><span class="toc-number">1.8.8.</span> <span class="toc-text">8.取得平均薪水最高的部门的部门编号</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#9-%E5%8F%96%E5%BE%97%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E6%9C%80%E9%AB%98%E7%9A%84%E9%83%A8%E9%97%A8%E7%9A%84%E9%83%A8%E9%97%A8%E7%9A%84%E5%90%8D%E7%A7%B0"><span class="toc-number">1.8.9.</span> <span class="toc-text">9.取得平均薪水最高的部门的部门的名称</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#10-%E6%B1%82%E5%B9%B3%E5%9D%87%E8%96%AA%E6%B0%B4%E7%9A%84%E7%AD%89%E7%BA%A7%E6%9C%80%E4%BD%8E%E7%9A%84%E9%83%A8%E9%97%A8%E5%90%8D%E7%A7%B0"><span class="toc-number">1.8.10.</span> <span class="toc-text">10.求平均薪水的等级最低的部门名称</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#11-%E5%8F%96%E5%BE%97%E6%AF%94%E6%99%AE%E9%80%9A%E5%91%98%E5%B7%A5%E7%9A%84%E6%9C%80%E9%AB%98%E8%96%AA%E6%B0%B4%E8%BF%98%E8%A6%81%E9%AB%98%E7%9A%84%E9%A2%86%E5%AF%BC%E4%BA%BA%E5%A7%93%E5%90%8D"><span class="toc-number">1.8.11.</span> <span class="toc-text">11.取得比普通员工的最高薪水还要高的领导人姓名</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#12-%E5%8F%96%E5%BE%97%E8%96%AA%E6%B0%B4%E6%9C%80%E9%AB%98%E7%9A%84%E5%89%8D%E4%BA%94%E5%90%8D%E5%91%98%E5%B7%A5"><span class="toc-number">1.8.12.</span> <span class="toc-text">12.取得薪水最高的前五名员工</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#13-%E5%8F%96%E5%BE%97%E8%96%AA%E6%B0%B4%E6%9C%80%E9%AB%98%E7%9A%84%E7%AC%AC%E5%85%AD%E5%88%B0%E7%AC%AC%E5%8D%81%E5%90%8D%E5%91%98%E5%B7%A5"><span class="toc-number">1.8.13.</span> <span class="toc-text">13.取得薪水最高的第六到第十名员工</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#14-%E5%8F%96%E5%BE%97%E6%9C%80%E5%90%8E%E5%85%A5%E8%81%8C%E7%9A%845%E5%90%8D%E5%91%98%E5%B7%A5"><span class="toc-number">1.8.14.</span> <span class="toc-text">14.取得最后入职的5名员工</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#15-%E5%8F%96%E5%BE%97%E6%AF%8F%E4%B8%AA%E8%96%AA%E6%B0%B4%E7%AD%89%E7%BA%A7%E6%9C%89%E5%A4%9A%E5%B0%91%E5%91%98%E5%B7%A5"><span class="toc-number">1.8.15.</span> <span class="toc-text">15.取得每个薪水等级有多少员工</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#16-%E9%9D%A2%E8%AF%95%E9%A2%98"><span class="toc-number">1.8.16.</span> <span class="toc-text">16.面试题</span></a></li></ol></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2024/02/13/%E8%8B%B1%E6%96%87%E5%8D%95%E8%AF%8D%E5%AD%A6%E4%B9%A0/" title="英文单词学习">英文单词学习</a><time datetime="2024-02-13T05:10:38.000Z" title="发表于 2024-02-13 13:10:38">2024-02-13</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2024/02/10/html%E4%BB%A5%E5%8F%8Acss%E5%AD%A6%E4%B9%A0/" title="html以及css学习">html以及css学习</a><time datetime="2024-02-10T13:35:11.000Z" title="发表于 2024-02-10 21:35:11">2024-02-10</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2024/02/10/vue%E5%AD%A6%E4%B9%A0/" title="vue学习">vue学习</a><time datetime="2024-02-10T08:35:56.000Z" title="发表于 2024-02-10 16:35:56">2024-02-10</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2024/02/10/ER%E5%9B%BE/" title="ER图">ER图</a><time datetime="2024-02-10T03:40:24.000Z" title="发表于 2024-02-10 11:40:24">2024-02-10</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2024/02/08/python%E5%AD%A6%E4%B9%A0/" title="python学习">python学习</a><time datetime="2024-02-08T04:11:12.000Z" title="发表于 2024-02-08 12:11:12">2024-02-08</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2024 By zjaaa</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside-config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><span class="scroll-percent"></span><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/js/utils.js?v=4.13.0"></script><script src="/js/main.js?v=4.13.0"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui@5.0.33/dist/fancybox/fancybox.umd.min.js"></script><script src="https://cdn.jsdelivr.net/npm/vanilla-lazyload@17.8.8/dist/lazyload.iife.min.js"></script><div class="js-pjax"></div><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>